package dao.impl;

import bean.user_info;
import bean.wareHouse;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import dao.wareHouseDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import utils.c3p0Utils;

import java.sql.SQLException;
import java.util.List;


public class wareHouseImpl implements wareHouseDao {
    @Override
    public int wareHouseAdd(String wareHouseName, int wareHouseCap) {
        String sql="INSERT INTO WareHouse (warehousename,warehousecap,isalive) VALUES (?,?,1)";
        QueryRunner qr=new QueryRunner(c3p0Utils.getDataSource());
        int i=0;
        try {
            i=qr.update(sql,wareHouseName,wareHouseCap);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public int wareHouseDel(int warehouseId) {
        QueryRunner qr=new QueryRunner(c3p0Utils.getDataSource());
        int i=0;
        try {
            i=qr.update("update WareHouse set isalive=? where warehouseId = ?",0,warehouseId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;

    }

    @Override
    public int wareHouseChange(int wareHouseId,String wareHouseName, int wareHouseCap) {
        QueryRunner qr=new QueryRunner(c3p0Utils.getDataSource());
        int i=0;
        try {
            i=qr.update("update WareHouse set warehousename=?,warehousecap=? where warehouseId=?",wareHouseName,wareHouseCap,wareHouseId);//
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public List<wareHouse> whList() {

        QueryRunner qr=new QueryRunner(c3p0Utils.getDataSource());
        String sql1="SELECT SUM(merchSum) ADDmerchSum FROM Repertory WHERE warehouseId=?";
        List<wareHouse> list=null;
        try {
            list=qr.query("select * from WareHouse where isalive=?",new BeanListHandler<wareHouse>(wareHouse.class),1);
            for (int i=0;i<list.size();i++){
                wareHouse w=qr.query(sql1,new BeanHandler<wareHouse>(wareHouse.class),list.get(i).getWarehouseId());
                list.get(i).setSurplusCapacity(list.get(i).getWarehouseCap()-w.getADDmerchSum());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public wareHouse wareHouseSingal(int wareHouseId) {
        QueryRunner qr=new QueryRunner(c3p0Utils.getDataSource());
        wareHouse wh=null;
        try {
            wh=qr.query("select * from WareHouse where warehouseId=? and isalive=?",new BeanHandler<wareHouse>(wareHouse.class),wareHouseId,1);//

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return wh;
    }

    @Override
    public List<wareHouse> wareHouseOne(String wareHouseName) {
        QueryRunner qr=new QueryRunner(c3p0Utils.getDataSource());
        List<wareHouse> whOne= null;
        try {
            whOne=qr.query("select * from WareHouse where warehousename=? and isalive=?",new BeanListHandler<wareHouse>(wareHouse.class),wareHouseName,1);//

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return whOne;
    }
}
